Win-Man's Blog
0%

MySQL Binlog(九)——MySQL字段存储格式——日期类型字段类型

Posted on Edited on In

MySQL Binlog(九)——MySQL字段存储格式——日期类型字段类型

前言

本文继续介绍binlog中日期类型字段的存储格式。

日期型字段类型

DATE,DATETIME,TMIESTAMP类型

  • DATE

日期。支持的范围是‘1000-01-01’到‘9999-12-31’。MySQL使用’YYYY-MM-DD’格式显示DATE值。但允许使用字符串或数字给DATE列赋值。

  • DATETIME[(fsp)]

日期和时间的组合。支持的范围是‘1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999’。MySQL使用’YYYY-MM-DD HH:MM:SS[.fraction]’格式显示DATETIME值，但允许使用字符串或数字给DATETIME列赋值。

从MySQL5.6.4开始，一个可选的范围从0到6的fsp值可以指定秒数的精度。值为0表示没有小数部分。如果省略的话，默认精度为0.

从MySQL5.6.5开始，自动化初始和更新到当前日期时间，可以使用DATETIME列的DEFAULT和ON UPDATE定义项。

  • TIMESTAMP[(fsp)]

时间戳。范围从’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。TIMESTAMP存储从纪元(‘1970-01-01 00:00:00’UTC)至今的总秒数。

TIME类型

  • TIME[(fsp)]

时间。范围从’-838:59:59.000000’到’838:59:59.000000’。MySQL使用’HH:MM:SS[.fraction]’格式显示TIME值。但允许使用字符串或数字给TIME列赋值。

从MySQL5.6.4开始，一个可选的范围从0到6的fsp值可以指定秒数的精度。值为0表示没有小数部分。如果省略的话，默认精度为0。

YEAR类型

  • YEAR[(2|4)]

两位或四位格式的年。默认是四位格式，虽然显示上YEAR(2)或YEAR(4)的格式不同，但他们具有相同的范围的值。四位格式显示为1901至2155，和0000。两位格式显示为70至69，表示1970至2069。MySQL以为YYYY或YY格式显示YEAR值，但允许使用字符串或数字赋值。

日期型数据存储格式

解析环境描述

表结构定义

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `time_table` (
  `col1` date DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` datetime(3) DEFAULT NULL,
  `col4` timestamp NULL DEFAULT NULL,
  `col5` timestamp(4) NULL DEFAULT NULL,
  `col6` time DEFAULT NULL,
  `col7` time(5) DEFAULT NULL,
  `col8` year(4) DEFAULT NULL,
  `col9` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

表数据展示

1
2
3
4
5
6
7
8
9
root@localhost : gangshen 07:04:33> insert into time_table values('2017-12-14','2017-12-14 09:54:00','2017-12-14 09:54:00.112','2017-12-14 09:54:00','2017-12-14 09:54:00.1113','09:54:00','09:54:00.00000','2017','2017');
Query OK, 1 row affected (0.03 sec)

root@localhost : gangshen 07:04:47> select * from time_table;                                                                                                          +------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
| col1      | col2                | col3                    | col4                | col5                    | col6    | col7          | col8 | col9 |
+------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
| 2017-12-14 | 2017-12-14 09:54:00 | 2017-12-14 09:54:00.112 | 2017-12-14 09:54:00 | 2017-12-14 09:54:00.1113 | 09:54:00 | 09:54:00.00000 | 2017 | 2017 |
+------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
1 row in set (0.00 sec)

然后从binlog文件中拿到，对应的Table_map_event和Writes_rows_event对应的字节内容，开始解析

元数据解析

Table_map_event字节数据解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
公共头部部分省略：
4e 00 00 00 00 00 //table_id :小端存储，16进制转换为10进制为78
01 00 //flag :
08 67 61 6e 67 73 68 65 6e 00 //database_name :1个字节是字符串长度，后接一个null-terminated string 第一个字节表示字符串长度为8，后面内容将16进制转换为ascii字符为gangshen
0a 74 69 6d 65 5f 74 61 62 6c 65 00 //table_name: 1个字节是字符串长度，后接一个null-terminated string第一个字节表示字符串长度为5，后面内容将16进制转换为ascii字符为time_table
09 //columns count :packet integer类型，转换之后，数值为9 表示表中有9个字段
0a 12 12 11 11 13 13 0d 0d //column type
06 //metadata_length: packet integer类型,转换之后，数值为9，表示记录表中的metadata内容占用9个字节
00 //col2
03 //col3
00 //col4
04 //col5
00 //col6
05 //col7
ff 01 //null_bits :int((column_count + 7) / 8)个字节 一个bit表示一个字段是否可以为null
38 9b 0a 26 //checksum

从Table_map_event中可以按照上面的讲述，解析出表中所有的字段类型以及对应的元数据，按照顺序分别是：

第一个字段：0x0a=MYSQL_TYPE_DATE 无元数据

第二个字段：0x12=MYSQL_TYPE_DATETIME2 元数据0x00表示该字段的时间精度为0

第三个字段：0x12=MYSQL_TYPE_DATETIME2 元数据0x03表示该字段的时间精度为3

第四个字段：0x11=MYSQL_TYPE_TIMESTAMP2 元数据0x00表示该字段的时间精度为0

第五个字段：0x11=MYSQL_TYPE_TIMESTAMP2 元数据0x04表示该字段的时间精度为4

第六个字段：0x13=MYSQL_TYPE_TIME2 元数据0x00表示该字段的时间精度为0

第七个字段：0x13=MYSQL_TYPE_TIME2 元数据0x05表示该字段的时间精度为5

第八个字段：0x0d=MYSQL_TYPE_YEAR 无元数据

第九个字段：0x0d=MYSQL_TYPE_YEAR 无元数据

日期型数据“值”解析

Write_rows_log_event字节数据解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
公共头部部分省略
4e 00 00 00 00 00 //table_id: 小端存储，16进制转换为10进制为78
01 00 //flag:
02 00 //var header length :小端存储，16进制转换为10进制为2
09 //m_width :packet integer，表示表中字段数量
ff ff //before image: (m_width + 7) / 8字节
00 fe //bitmap_bits :表中两个字段，插入的值都不为NULL
8e c3 0f //col1
99 9e 5c 9d 80 //col2
99 9e 5c 9d 80 04 60 //col3
5a 31 d9 b8 //col4
5a 31 d9 b8 04 59 //col5
80 9d 80 //col6
80 9d 80 00 00 00 //col7
75 //col8
75 //col9
7e da 55 ce //checksum

MYSQL_TYPE_DATE字段解析

MYSQL_TYPE_DATE类型使用3个字节存储，小端存储。3个字节一共24个比特位，其中从低到高（从右到左）前5位，表示日期，接着的从低到高4位表示月份，剩余的位数表示年份。

在上面的例子中，col1的类型就是MYSQL_TYPE_DATE，所以在Write_rows_log_event中占用3个字节的内容，为0x8ec30f，因为是小端存储，所以实际顺序为0x0fc38e,3个字节一共24个比特位，从右往左，5位表示日期，4位表示月份，15位表示年份。将0x0fc38e转换成二进制是b’0000 1111 1100 0011 1000 1110’，所以day=b’01110’=14，month=b‘1100’=12，year=’0000 1111 1100 001’=2017，所以对应的字段的值是‘2017-12-14’

MYSQL_TYPE_DATETIME2字段解析

MYSQL_TYPE_DATETIME2类型使用5个字节存储，并且根据时间精度的不同，额外需要不同的字节存储数据,时间精度部分为大端存储。

年月日时分秒的内容存储在基础的5个字节中，5个字节一共40位，从左往右前18位表示年月（年份 * 13 + 月份），其中最高位表示符号，最高位为1表示是正的，为0表示是负的，接着的5位表示日期，剩余的17位中，按照顺序，5位表示时，6位表示分，6位表示秒。因为时间精度最多为6位，一个字节表示两位，所以时间精度为1或者2的时候，使用一个额外的字节表示时间精度，时间精度为3或者4的时候，使用两个额外的字节表示时间精度，时间精度为5或者6的时候，使用三个额外的字节表示时间精度。

时间精度 所需字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3

在上面的例子中，col2和col3的类型就是MYSQL_TYPE_DATETIME2，因为col2的时间精度为0，所以col2在Write_rows_log_event中占用5个字节的内容，为0x999e5c9d80，因为col3的时间精度为3，所以col3在Write_rows_log_event中占用（5+2）个字节的内容，为0x999e5c9d800460。

我们先解析col2字段的值，从上面的描述中，5个字节（40位）内容，前18位表示年月（年份 * 13+月份），5位表示日期，5位表示时，6位表示分，6位表示秒。0x999e5c9d80转换为二进制是b‘1001 1001 1001 1110 0101 1100 1001 1101 1000 0000’，年月=b’1001 1001 1001 1110 01’ ，日期=b’01110’，时=b’01001’，

分=b’110110’，秒=b’000000’。年月中最高位符号位（正数最高位为1，负数最高位为0），所以在计算数值的时候，要排除最高位的影响（将基础的5个字节减去0x8000000000），即年月值的实际二进制为b’0001 1001 1001 1110 01’，转换为10进制为26233。年=26233/13，月=26233%13,日=b’01110’=14,时=b’01001’=9,分=b’110110’=54,秒=b’000000’=0。所以col2字段的值为2017-12-14 09:54:00

接着解析col3字段值，因为col2和col3字段的基础5个字节的内容一致，所以直接跳过，解析额外的时间精度部分，时间精度部分为0x0460,因为是大端存储，所以转换为10进制结果为1120，即时间精度部分为1120。所以col3字段的值为2017-12-14 09:54:00.1120

MYSQL_TYPE_TIMESTAMP2字段解析

MYSQL_TYPE_TIMESTAMP2类型使用4个字节存储,大端存储，并且根据时间精度的不同，额外需要不同的字存储数据，时间精度部分也使用大端存储；如果时间精度为1或者2，则4个字节之外还需要1个字节存储数据；如果时间精度为3或者4，则4个字节之外还需要2个字节存储数据；如果时间精度为5或者6，则4个字节之外还需要3个字节存储数据。

时间精度 所需字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3

在上面的例子中，col4和col5的类型就是MYSQL_TYPE_TIMESTAMP2，因为col4的时间精度为0，所以col4在Write_rows_log_event中占用4个字节的内容，为0x5a31d9b8，因为col5的时间精度为4，所以col5在Write_rows_log_event中占用（4+2）=6个字节，为0x5a31d9b80459。

我们先解析col4字段的值，4个字节，大端存储，所以0x5a31d9b8转换为10进制数为1513216440，即col4的时间戳为1513216440（时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数），转换为时间为2017-12-14 09:54:00

接着解析col5字段的值，因为col4和col5字段前4个字节内容相同，故不重新解析，直接解析时间精度部分，时间精度为0x0459，因为是大端存储，所以转换为10进制为1113，即时间精度部分为1113。所以col5字段的值为2017-12-14 09:54:00.1113

MYSQL_TYPE_TIME2字段解析

MYSQL_TYPE_TIME2类型使用3个字节存储,大端存储，并且根据时间精度的不同，额外需要不同的字节存储数据,时间精度部分使用大端存储。

时分秒的内容存储在基础的3个字节中，3个字节一共24位，从左往右，前12位表示时，中间6位表示分，最后6位表示秒，额外的字节存储秒的时间精度部分。因为时间精度最多为6位，一个字节表示两位，所以时间精度为1或者2的时候，使用一个额外的字节表示时间精度，时间精度为3或者4的时候，使用两个额外的字节表示时间精度，时间精度为5或者6的时候，使用三个额外的字节表示时间精度。

时间精度 所需字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3

MYSQL_TYPE_TIME2类型字段值可以为负数。那么MYSQL_TYPE_TIME2类型是如何存储正负值的呢。

  • 没有时间精度

在没有时间精度的情况下，MYSQL_TYPE_TIME2类型使用3个字节存储时分秒内容(大端存储)。MySQL使用0x800000作为时间原点（00:00:00），比0x800000大的为正的时间值，比0x800000小的为负的时间值。即时分秒的内容是与0x800000做减法之后的绝对值。

  • 时间精度为1或者2

在时间精度为1或者2的情况下，MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储)，使用一个额外的字节存储时间精度。MySQL使用0x80000000作为时间原点（00:00:00.00），比0x80000000大的为正的时间值，比0x80000000小的为负的时间值，即时分秒的即时间精度的内筒是与0x80000000做减法之后的绝对值。

  • 时间精度为3或者4

在时间精度为1或者2的情况下，MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储)，使用两个额外的字节存储时间精度。MySQL使用0x8000000000作为时间原点（00:00:00.0000），比0x8000000000大的为正的时间值，比0x8000000000小的为负的时间值，即时分秒的即时间精度的内筒是与0x8000000000做减法之后的绝对值。

  • 时间精度为5或者6

在时间精度为1或者2的情况下，MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储)，使用三个额外的字节存储时间精度。MySQL使用0x800000000000作为时间原点（00:00:00.000000），比0x800000000000大的为正的时间值，比0x800000000000小的为负的时间值，即时分秒的时间精度是与0x800000000000做减法之后的绝对值。

在上面的例子中，col6和col7的类型就是MYSQL_TYPE_TIME2，因为col6的时间精度为0，所以col6在Write_rows_log_event中占用3个字节的内容，为0x809d80，因为col7的时间精度为5，所以col7在Write_rows_log_event中占用（3+3）=6个字节的内容，为0x809d80000000。

我们先解析col6的值，按照上面的描述，col6字段类型为MYSQL_TYPE_TIME2，时间精度为0，所以使用3个字节存储内容，在Write_rows_log_event中占用3个字节的内容，为0x809d80。因为时间精度为0的情况下，MySQL是以0x800000为时间原点，具体的时分秒为0x809d80与0x800000差值的绝对值=0x009d80。且0x809d80比0x800000大，所以字段值符号为正。时分秒=0x009d80=b‘0000 0000 1001 1101 1000 0000’，时=前12位=b’0000 0000 1001’=9，分=中间6位=b’1101 10’=54，秒=最后6位=b’00 0000’=0，即col6字段的值为09:54:00

接着解析col7的值，按照上面的描述，col7字段类型为MYSQL_TYPE_TIME2，时间精度为5，所以使用3+3=6个字节存储内容，在Write_rows_log_event中占用6个字节的内容，为0x809d80000000，因为时间精度为5的情况下，MySQL是以0x800000000000为时间原点，具体的时分秒以及时间精度存储为0x809d80000000与0x800000000000差值的绝对值=0x009d80000000。且0x809d80000000比0x800000000000大，所以字段值符号为正。时分秒=0x009d80=b‘0000 0000 1001 1101 1000 0000’，时=前12位=b’0000 0000 1001’=9，分=中间6位=b’1101 10’=54，秒=最后6位=b’00 0000’=0，时间精度=0x000000=0，即col7字段的值为09:54:00.00000

MYSQL_TYPE_YEAR字段解析

MYSQL_TYPE_DATA类型使用1个字节存储。表示的值是（要表示的年份-1900）

在上面的例子中，col8和col9的类型就是MYSQL_TYPE_YEAR，col8和col9都在Write_rows_log_event中占用1个字节的内容，分别为0x75和0x75，转换为10进制为117，将结果加上1900，则最终col8和col9字段的值为2017。